#!/usr/bin/env python
# -*- coding:utf-8 -*-

import traceback
import mysql.connector
from utils.log import logger



class MyDB:
    """动作类，获取数据库连接，配置数据库IP，端口等信息，获取数据库连接"""

    def __init__(self, db_name='', db_host='', port=3306, user='', password='', charset='utf8'):
        try:
            self.dbconn = None
            self.host = db_host
            self.port = port
            self.user = user
            self.passwd = password
            self.db_name = db_name
            self.charset = charset
            self.connect_config = {'host': self.host, 'port': self.port, 'user': self.user, 'password': self.passwd,
                                   'database': self.db_name, 'charset': self.charset}

            self.__connect_database()
            logger.debug('初始化数据库连接成功(数据库：%s)' % self.db_name)
        except Exception as e:
            raise Exception('初始化数据库(%s)连接失败：%s' % (self.db_name, traceback.format_exc()))

    def __connect_database(self):
        self.dbconn = mysql.connector.connect(**self.connect_config)

    def get_host(self):
        return self.host

    def get_port(self):
        return self.port

    def create(self, query):
        try:
            db_cursor = self.dbconn.cursor()
        except Exception:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库' % traceback.format_exc())
            self.__connect_database()
            db_cursor = self.dbconn.cursor()

        try:
            db_cursor.execute(query)
            db_cursor.execute('commit')
            db_cursor.close()
        except Exception:
            db_cursor.execute('rollback')
            db_cursor.close()
            raise Exception('创建数据库表操作失败：%s' % traceback.format_exc())

    def insert(self, query, params=None):
        '''插入单条数据
        示例：
        :query "INSERT INTO employees (name, phone) VALUES ('%s','%s')"
        :params ('Jane','555-001')
        '''

        try:
            db_cursor = self.dbconn.cursor()
        except Exception:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库' % traceback.format_exc())
            self.__connect_database()
            db_cursor = self.dbconn.cursor()

        try:
            db_cursor.execute(query, params)
            record_id = db_cursor.lastrowid
            db_cursor.execute('commit')
            db_cursor.close()
            return record_id
        except Exception:
            db_cursor.execute('rollback')
            db_cursor.close()
            raise Exception('执行数据库插入操作失败：%s' % traceback.format_exc())

    def insert_many(self, query, seq_params):
        '''批量插入

        示例：批量插入 3条数据
        :query "INSERT INTO employees (name, phone) VALUES (%s, %s)"
        :seq_params [
            ('Jane','555-001'),
            ('Joe', '555-001'),
            ('John', '555-003')
            ]
        注意：针对字符串值，%s左右不能加 ' 号，否则会语法错误，如上，如果%s左右添加 ' , 执行插入时会替换为
        INSERT INTO employees (name, phone) VALUES (''Jane'', ''555-001'')

        注意：针对整数值，%s可以加 ' 号，也可以不加，如下，假设id,age列为整型
        示例：批量插入 2条数据
        :query "INSERT INTO users (id, age, phone) VALUES (%d, %d, %s)"
        :seq_params [
            (1， 28, '13510685685'),
            (3, 29, '13510685685'),
            ]

        :query "INSERT INTO users (id, age, phone) VALUES ('%s', '%s', %s)"
        :seq_params [
            (1， 28, '13510685685'),
            (3, 29, '13510685685'),
            ]
        不知道为啥，实践时发现，有时候，数据列同时有字符型和整型的情况下，整型数据列使用使用 %d 作为占位符，会报错，提示参数值没有全部被使用(Not all parameters were used in the SQL statement)，解决方式就是整型数据列使用 '%s' 作为占位符，如上
        '''

        try:
            db_cursor = self.dbconn.cursor()
        except Exception as e:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库' % e)
            self.__connect_database()
            db_cursor = self.dbconn.cursor()

        try:
            db_cursor.executemany(query, seq_params)
            record_id = db_cursor.lastrowid
            db_cursor.execute('commit')
            db_cursor.close()
            return record_id
        except Exception:
            db_cursor.execute('rollback')
            db_cursor.close()
            raise Exception('执行数据库批量插入操作失败：%s' % traceback.format_exc())

    def delete(self, query, params=None):
        try:
            db_cursor = self.dbconn.cursor()
        except Exception:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库' % traceback.format_exc())
            self.__connect_database()
            db_cursor = self.dbconn.cursor()
        try:
            db_cursor.execute(query, params)
            db_cursor.execute('commit')
            db_cursor.close()
        except Exception:
            db_cursor.execute('rollback')
            db_cursor.close()
            raise Exception('执行数据库删除操作失败：%s' % traceback.format_exc())

    def update(self, query):
        try:
            db_cursor = self.dbconn.cursor()
        except Exception:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库' % traceback.format_exc())
            self.__connect_database()
            db_cursor = self.dbconn.cursor()
        try:
            db_cursor.execute(query)
            db_cursor.execute('commit')
            db_cursor.close()
        except Exception:
            db_cursor.execute('rollback')
            db_cursor.close()
            raise Exception('执行数据库更新操作失败：%s' % traceback.format_exc())

    def select(self, query, params=None, dictionary=False):
        '''查询结果最多只包含一条记录
        示例：查询获取获取id为5的记录
        :query  "SELECT * FROM t1 WHERE id = %s"
        :param (5,)

        当然，也可以把参数放到query中 SELECT * FROM t1 WHERE id = 5
        '''

        result = []
        if dictionary:
            result = {}
        try:
            db_cursor = self.dbconn.cursor(dictionary=dictionary)
        except Exception:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库' % traceback.format_exc())
            self.__connect_database()
            db_cursor = self.dbconn.cursor(dictionary=dictionary)

        try:
            db_cursor.execute(query, params)
            query_result = db_cursor.fetchall()
            if query_result:
                result = query_result[0]

            if not dictionary:
                temp_list = []
                for item in query_result:
                    if type(item) == type(bytearray(b'')):  # 转换字节数组为字符串
                        item = item.decode('utf-8')
                    temp_list.append(item)
                result = temp_list
            db_cursor.close()
        except Exception:
            db_cursor.close()
            raise Exception('执行数据库查询操作失败：%s' % traceback.format_exc())

        return result

    def select_many(self, query, params=None, dictionary=False):
        '''查询，查询结果包含多条记录'''

        query_result = []

        try:
            db_cursor = self.dbconn.cursor(dictionary=dictionary)
        except Exception:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库(%s)' % (traceback.format_exc(), self.db_name))
            self.__connect_database()

            db_cursor = self.dbconn.cursor(dictionary=dictionary)

        try:
            db_cursor.execute(query, params)
            query_result = db_cursor.fetchall()
            if not dictionary:
                final_result = []
                for record in query_result:
                    temp_result = []
                    for item in record:
                        if type(item) == type(bytearray(b'')):  # 转换字节数组为字符串类型
                            item = item.decode('utf-8')
                        temp_result.append(item)
                    final_result.append(temp_result)
                query_result = final_result
            db_cursor.close()
        except Exception:
            db_cursor.close()
            raise Exception('执行数据库查询操作失败：%s' % traceback.format_exc())

        return query_result

    def execute_many(self, query):
        '''
        执行多条数据库操作，比如 SELECT 1; INSERT INTO t1 VALUES (); SELECT 2
        '''

        try:
            db_cursor = self.dbconn.cursor()
        except Exception:
            print('获取数据库游标失败：%s，正在尝试重新连接数据库' % traceback.format_exc())
            self.__connect_database()
            db_cursor = self.dbconn.cursor()
        try:
            for result in db_cursor.execute(query, multi=True):
                if result.with_rows:
                    # print("Rows produced by statement '{}':".format(
                    #    result.statement))
                    result.fetchall()
                else:
                    pass
                    # print("Number of rows affected by statement '{}': {}".format(
                    #     result.statement, result.rowcount))
            db_cursor.execute('commit')
            db_cursor.close()
        except Exception:
            db_cursor.execute('rollback')
            db_cursor.close()
            raise Exception('批量执行数据库操作失败：%s' % traceback.format_exc())

    def call_proc(self, query):
        '''调用存储过程'''

        try:
            db_cursor = self.dbconn.cursor()
        except Exception:
            logger.error('获取数据库游标失败：%s，正在尝试重新连接数据库' % traceback.format_exc())
            self.__connect_database()
            db_cursor = self.dbconn.cursor()

        try:
            for result in db_cursor.execute(query, multi=True):
                if result.with_rows:
                    # print("Rows produced by statement '{}':".format(
                    #    result.statement))
                    result.fetchall()
                else:
                    pass
                    # print("Number of rows affected by statement '{}': {}".format(
                    #     result.statement, result.rowcount))
            db_cursor.execute('commit')
            db_cursor.close()
        except Exception:
            db_cursor.execute('rollback')
            db_cursor.close()
            raise Exception('执行数据库存储过程失败：%s' % traceback.format_exc())

    def close(self):
        if self.dbconn:
            self.dbconn.close()
            self.dbconn = None

    def __del__(self):
        self.close()